Attribute VB_Name = "SvcStandTab"
'WRITTEN BY JALISA GREENE
'LAST MODIFIED BY JALISA GREENE
'LAST MODIFIED 6/10/2014

Dim Col As Long

Sub ServiceStandardizationTab()
'**THIS SUB GENERATES THE REGULAR SERVICE STANDARDIZATION TAB SHOWING PROPOSED AND CURRENT SERVICE LEVELS FOR EACH ACHIEVED SERVICE STANDARDIZATION**'

    Col = RGB(131, 189, 235)
    msg = MsgBox("This file may take up to 10 minutes to generate. During this time Excel will be inaccessible, please make sure you save before you begin.", vbOKCancel, "Service Standardization Tab Output")
    If msg = 1 Then 'the user clicked okay
        Application.ScreenUpdating = False
        Worksheets("Service Standardization").Activate
        ActiveSheet.Rows("17:2000").ClearContents
        ActiveSheet.Rows("17:2000").ClearFormats
        ActiveSheet.Rows("17:2000").Font.Name = "Helvetica"
        ActiveSheet.Rows("17:2000").RowHeight = 20
        Range("A17").Select
        lastRow = Worksheets("ASR").Range("A1099").End(xlUp).Row
        'copy location id values for those who have new contracts proposed'
        'look in cell CMQ for a boolean for if there is SS or not
        Dim loc As Range
        For Each loc In Worksheets("ASR").Range("A8", "A" & lastRow)
            rowNum = WorksheetFunction.Match(loc.Value, Worksheets("ASR").Range("A8", "A" & lastRow), 0) + 7
            If Worksheets("ASR").Range("CMQ" & rowNum).Value = True Then
                 ActiveCell.Value = loc.Value
                 ActiveCell.HorizontalAlignment = xlLeft
                 ActiveCell.Offset(1).Select
            End If
        Next
        
        Range("A17", "A" & ActiveCell.Offset(-1).Row).Sort key1:=Range("A17")
        
        ActiveCell.Value = "0" 'end of file marker'
        
        'find the last row in our current sheet
        lastRow = Worksheets("Service Standardization").Range("A1099").End(xlUp).Row
        'for each location, going to the last row minus 1 because we put a 0 for end of file.
        For Each loc In Range("A17", "A" & lastRow - 1)
            If TypeName(loc.Value) = "Double" Then
                loc.Select
                rowNum = WorksheetFunction.Match(loc.Value, Worksheets("ASR").Range("A:A"), 0)
            'insert 3 lines for address and separator line'
                ActiveCell.Offset(1).Insert
                ActiveCell.Offset(1).Insert
                ActiveCell.Offset(1).Insert
                
                'Insert address'
                ActiveCell.Offset(1).Value = Worksheets("ASR").Range("C" & rowNum).Value
                ActiveCell.Offset(2).Value = Worksheets("ASR").Range("K" & rowNum).Value
                
                'If Container 1 has SS
                If Worksheets("ASR").Range("CMR" & rowNum) = True Then
                    ActiveCell.Offset(0, 1).Value = Worksheets("ASR").Range("AA" & rowNum).Value
                    ActiveCell.Offset(0, 2).Value = Worksheets("ASR").Range("BC" & rowNum).Value
                    ActiveCell.Offset(0, 3).Value = Worksheets("ASR").Range("AK" & rowNum).Value
                    ActiveCell.Offset(0, 4).Value = Worksheets("ASR").Range("BBL" & rowNum).Value
                    ActiveCell.Offset(0, 5).Value = Worksheets("ASR").Range("BBO" & rowNum).Value
                    ActiveCell.Offset(1).Select
                End If
                
                'If Container 2 has SS'
                If Worksheets("ASR").Range("CMS" & rowNum) = True Then
                    ActiveCell.Offset(0, 1).Value = Worksheets("ASR").Range("CA" & rowNum).Value
                    ActiveCell.Offset(0, 2).Value = Worksheets("ASR").Range("DC" & rowNum).Value
                    ActiveCell.Offset(0, 3).Value = Worksheets("ASR").Range("CK" & rowNum).Value
                    ActiveCell.Offset(0, 4).Value = Worksheets("ASR").Range("BCS" & rowNum).Value
                    ActiveCell.Offset(0, 5).Value = Worksheets("ASR").Range("BCV" & rowNum).Value
                    ActiveCell.Offset(1).Select
                End If
                
                'If Container 3 has SS
                If Worksheets("ASR").Range("CMT" & rowNum) = True Then
                    ActiveCell.Offset(0, 1).Value = Worksheets("ASR").Range("EA" & rowNum).Value
                    ActiveCell.Offset(0, 2).Value = Worksheets("ASR").Range("FC" & rowNum).Value
                    ActiveCell.Offset(0, 3).Value = Worksheets("ASR").Range("EK" & rowNum).Value
                    ActiveCell.Offset(0, 4).Value = Worksheets("ASR").Range("BDZ" & rowNum).Value
                    ActiveCell.Offset(0, 5).Value = Worksheets("ASR").Range("BEC" & rowNum).Value
                    ActiveCell.Offset(1).Select
                End If
                
                'If Container 4 has SS
                If Worksheets("ASR").Range("CMU" & rowNum) = True Then
                    If TypeName(ActiveCell.Offset(1).Value) = "Double" Then ActiveCell.Offset(1).EntireRow.Insert
                    ActiveCell.Offset(0, 1).Value = Worksheets("ASR").Range("GA" & rowNum).Value
                    ActiveCell.Offset(0, 2).Value = Worksheets("ASR").Range("HC" & rowNum).Value
                    ActiveCell.Offset(0, 3).Value = Worksheets("ASR").Range("GK" & rowNum).Value
                    ActiveCell.Offset(0, 4).Value = Worksheets("ASR").Range("BFG" & rowNum).Value
                    ActiveCell.Offset(0, 5).Value = Worksheets("ASR").Range("BFJ" & rowNum).Value
                    ActiveCell.Offset(1).Select
                End If
                
                'If Container 5 has SS
                If Worksheets("ASR").Range("CMV" & rowNum) = True Then
                    If TypeName(ActiveCell.Offset(1).Value) = "Double" Then ActiveCell.Offset(1).EntireRow.Insert
                    ActiveCell.Offset(0, 1).Value = Worksheets("ASR").Range("IA" & rowNum).Value
                    ActiveCell.Offset(0, 2).Value = Worksheets("ASR").Range("JC" & rowNum).Value
                    ActiveCell.Offset(0, 3).Value = Worksheets("ASR").Range("IK" & rowNum).Value
                    ActiveCell.Offset(0, 4).Value = Worksheets("ASR").Range("BGN" & rowNum).Value
                    ActiveCell.Offset(0, 5).Value = Worksheets("ASR").Range("BGQ" & rowNum).Value
                    ActiveCell.Offset(1).Select
                End If
                
                'If Container 6 has SS
                If Worksheets("ASR").Range("CMW" & rowNum) = True Then
                    If TypeName(ActiveCell.Offset(1).Value) = "Double" And ActiveCell.Offset(1).Value <> loc.Value Then ActiveCell.Offset(1).EntireRow.Insert
                    ActiveCell.Offset(0, 1).Value = Worksheets("ASR").Range("KA" & rowNum).Value
                    ActiveCell.Offset(0, 2).Value = Worksheets("ASR").Range("LI" & rowNum).Value
                    ActiveCell.Offset(0, 3).Value = Worksheets("ASR").Range("KQ" & rowNum).Value
                    ActiveCell.Offset(0, 4).Value = Worksheets("ASR").Range("BHU" & rowNum).Value
                    ActiveCell.Offset(0, 5).Value = Worksheets("ASR").Range("BHX" & rowNum).Value
                    ActiveCell.Offset(1).Select
                End If
                
                'If Container 7 has SS
                If Worksheets("ASR").Range("CMX" & rowNum) = True Then
                    If TypeName(ActiveCell.Offset(1).Value) = "Double" And ActiveCell.Offset(1).Value <> loc.Value Then ActiveCell.Offset(1).EntireRow.Insert
                    ActiveCell.Offset(0, 1).Value = Worksheets("ASR").Range("MA" & rowNum).Value
                    ActiveCell.Offset(0, 2).Value = Worksheets("ASR").Range("NI" & rowNum).Value
                    ActiveCell.Offset(0, 3).Value = Worksheets("ASR").Range("MQ" & rowNum).Value
                    ActiveCell.Offset(0, 4).Value = Worksheets("ASR").Range("BJG" & rowNum).Value
                    ActiveCell.Offset(0, 5).Value = Worksheets("ASR").Range("BJJ" & rowNum).Value
                    ActiveCell.Offset(1).Select
                End If
                
                'If Container 8 has SS
                If Worksheets("ASR").Range("CMY" & rowNum) = True Then
                    If TypeName(ActiveCell.Offset(1).Value) = "Double" And ActiveCell.Offset(1).Value <> loc.Value Then ActiveCell.Offset(1).EntireRow.Insert
                    ActiveCell.Offset(0, 1).Value = Worksheets("ASR").Range("OA" & rowNum).Value
                    ActiveCell.Offset(0, 2).Value = Worksheets("ASR").Range("PI" & rowNum).Value
                    ActiveCell.Offset(0, 3).Value = Worksheets("ASR").Range("OQ" & rowNum).Value
                    ActiveCell.Offset(0, 4).Value = Worksheets("ASR").Range("BKS" & rowNum).Value
                    ActiveCell.Offset(0, 5).Value = Worksheets("ASR").Range("BKV" & rowNum).Value
                    ActiveCell.Offset(1).Select
                End If
                
                ActiveCell.Offset(-1, 6).Value = Worksheets("ASR").Range("Y" & rowNum).Value * 12
                loc.Select
                lastRow = Worksheets("Service Standardization").Range("A1099").End(xlUp).Row
                For Each aCell In Range("A17", "A" & lastRow - 1)
                    If TypeName(aCell.Value) = "Double" Then
                        While TypeName(ActiveCell.Offset(1).Value) <> "Double"
                            ActiveCell.Offset(1).Select
                        Wend
                        ActiveCell.Offset(-1).Select
                        Range(loc, "G" & ActiveCell.Row).Interior.Color = Col
                    End If
                Next
            End If
        Next
        Range("D:D").EntireColumn.HorizontalAlignment = xlCenter
        Range("F:F").EntireColumn.HorizontalAlignment = xlCenter
        Range("G:G").EntireColumn.HorizontalAlignment = xlRight
        Range("G:G").NumberFormat = "$#,#00.00"


        
        Range("A" & lastRow, "G" & lastRow).Select
        Selection.ClearContents
        Selection.Interior.ColorIndex = 1
        Selection.Font.ColorIndex = 2
        With Range("A" & lastRow)
            .Value = "Total: "
            .Font.Bold = True
            .Font.Size = 14
        End With
        With Range("G" & lastRow)
            .Value = Format(Worksheets("ASR").Range("Y1100").Value * 12, "Currency")
            .Font.Bold = True
            .Font.Size = 14
        End With
        Range("G15").HorizontalAlignment = xlCenter
        Range("A17").Select
    End If
    
End Sub












Sub FlatRateStandardization()
'**THIS SUB GENERATES THE FLAT RATE STANDARDIZATION TAB


msg = MsgBox("This file may take up to 10 minutes to generate. During this time Excel will be inaccessible, please make sure you save before you begin.", vbOKCancel, "Service Standardization Tab Output")
    If msg = 1 Then 'the user clicked okay
        Application.ScreenUpdating = False
        Worksheets("Flat-Rate Standardization").Activate
        Range("E11").Select
        'first find the cell that says Sales Volume so we can delete everything in between the header and it
        Cells.Find("Sales Volume", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True).Select
        
        Range("A12", "A" & ActiveCell.Offset(-1).Row).EntireRow.Delete
        Range("E11").Select
        ActiveCell.EntireRow.ClearContents
        
        lastRow = Worksheets("ASR").Range("A1099").End(xlUp).Row
        'copy location id values and sort'
        'look in cell CMQ for a boolean for if there is SS or not
        Dim loc As Range
        For Each loc In Worksheets("ASR").Range("A8", "A" & lastRow)
            rowNum = WorksheetFunction.Match(loc.Value, Worksheets("ASR").Range("A8", "A" & lastRow), 0) + 7
            If loc.Value <> "" And (Worksheets("ASR").Range("AA" & loc.Row).Value <> "" Or Worksheets("ASR").Range("KA" & loc.Row).Value <> "") Then   'affected locations first'
                 'insert in the location number, sales vol, and yds per week then format and go to the next cell down
                 ActiveCell.Value = loc.Value
                 ActiveCell.Offset(, 1).Value = Worksheets("ASR").Range("H" & rowNum).Value
                 ActiveCell.Offset(, 2).Value = Worksheets("ASR").Range("L" & rowNum).Value
                 ActiveCell.HorizontalAlignment = xlLeft
                 ActiveCell.Offset(1).EntireRow.Insert
                 ActiveCell.Offset(1).Select
            End If
        Next
        
        Range("E11", "E" & ActiveCell.Offset(-1).Row).Sort key1:=Range("E11")
        
        For Each loc In Worksheets("ASR").Range("A8", "A" & lastRow)
            rowNum = WorksheetFunction.Match(loc.Value, Worksheets("ASR").Range("A8", "A" & lastRow), 0) + 7
            If loc.Value <> "" And Worksheets("ASR").Range("AA" & loc.Row).Value = "" And Worksheets("ASR").Range("KA" & loc.Row).Value = "" Then    'unaffected locations last'
                'insert in the location number, sales vol then format and go to the next cell down
                ActiveCell.Value = loc.Value
                ActiveCell.Offset(, 1).Value = Worksheets("ASR").Range("H" & rowNum).Value
                ActiveCell.HorizontalAlignment = xlLeft
                ActiveCell.Offset(1).EntireRow.Insert
                ActiveCell.Offset(1).Select
             End If
        Next
        
        Set Rng = Range("E11", "G" & ActiveCell.Offset(-1).Row)
        With Rng.Borders
            .LineStyle = xlContinuous
            .Weight = xlThin
    End With
            
        
    Else
        Exit Sub
    End If
End Sub
